
project , uses(../data_output/legislators_klarner.dta)


	***********
	**CALIFORNIA
	** Life-time term limites: 6 x 2-year terms.
	** First year: 1996	
	***********
	
	** CA prep Klarner
	use ../data_output/legislators_klarner.dta, clear
	keep if state=="CA" & chamber=="house"
	keep if regexm(term, "1993|1995|1997|1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen mergename = proper(regexs(1)) if regexm(CandName,"^(.*),")
	replace mergename = "Robert Pacheco" if CandName=="PACHECO, ROBERT" & regexm(term, "1999|2001")
	replace mergename = "Rod Pacheco" if CandName=="PACHECO, ROD" & regexm(term, "1999|2001")
	replace mergename = "John Campbell" if CandName=="CAMPBELL, JOHN" & regexm(term, "2001")
	replace mergename = "Bill Campbell" if CandName=="CAMPBELL, BILL" & regexm(term, "2001")
	replace mergename = "Jerome Horton" if CandName=="HORTON, JEROME E." & regexm(term, "2003|2005")
	replace mergename = "Shirley Horton" if CandName=="HORTON, SHIRLEY" & regexm(term, "2003|2005")
	replace mergename = "Tom Berryhill" if CandName=="BERRYHILL, THOMAS" & regexm(term, "2009")
	replace mergename = "Bill Berryhill" if CandName=="BERRYHILL, BILL" & regexm(term, "2009|2011")
	replace mergename = "John A. Perez" if CandName=="PEREZ, JOHN A." & regexm(term, "2009|2011|2013")
	replace mergename = "V. Manuel Perez" if CandName=="PEREZ, MANUEL" & regexm(term, "2009|2011|2013")
	replace mergename = "Cristina Garcia" if CandName=="GARCIA, CRISTINA" & regexm(term, "2015")
	replace mergename = "Eduardo Garcia" if CandName=="GARCIA, EDUARDO" & regexm(term, "2015")
	replace mergename = "Eggman" if mergename=="Talamanteseggman" 
	replace mergename = "Negrete Mcleod" if mergename=="Negretemcleod" 
	replace mergename = "De La Torre" if mergename=="Delatorre" 
	replace mergename = "La Malfa" if mergename=="Lamalfa" 
	replace mergename = "La Suer" if mergename=="Lasuer" 
	replace mergename = "De Leon" if mergename=="Deleon" 
	replace mergename = "Beth Gaines" if mergename=="Gaines" & regexm(term, "2011|2013|2015")
	replace mergename = "Charles Calderon" if mergename=="Calderon" & regexm(term, "2007|2009|2011")
	replace mergename = "Ian Calderon" if mergename=="Calderon" & regexm(term, "2013")
	replace mergename = "Roger Hernandez" if mergename=="Hernandez" & regexm(term, "2013|2015")
	replace mergename = "Bonnie Lowenthal" if mergename=="Lowenthal" & regexm(term, "2009|2011")
	replace mergename = "Sharon Runner" if mergename=="Runner" & regexm(term, "2005|2007")
	replace mergename = "Audra Strickland" if mergename=="Strickland" & regexm(term, "2009")
	replace mergename = "Mark Stone" if mergename=="Stone" & regexm(term, "2015")
	replace mergename = "O'Donnell" if mergename=="Odonnell" & regexm(term, "2015")
	replace mergename = "Travis Allen" if mergename=="Allen" & regexm(term, "2015")
	
	gen lastname = trim(regexs(1)) if regexm(lower(CandName),"([^,]+)")
	gen firstname = trim(regexs(1)) if regexm(lower(CandName),",( [^ ]+)")
	bys chamber mergename term: gen n = _n
	by chamber mergename term: egen mean_n=mean(n)
	replace mergename = firstname +" " + lastname if mean_n>1  & regexm(term, "1993|1995|1997")  //firstname  + " " +  lastname //firstname+ ". "
	drop n mean_n
	replace mergename = lower(mergename)
	 // Jan Leja was never sworn in because of campaign finance violations. http://articles.latimes.com/2000/dec/02/news/mn-60115
	 // Richard Alarcon .Shortly after winning office, Alarcon announced his intention to seek a third term in 2007 as Los Angeles City Councilman in the 7th district, replacing Alex Padilla, who was resigning after winning a seat on State Senate.[2] In the special election, held in March, Alarcon won easily (receiving 54% to 29% for his closest opponent).[3] He resigned from the Assembly soon thereafter. His 102-day tenure in the Assembly was the shortest service in the California legislature since 1981, excluding one person elected but not sworn in
	// Don Perata ran for a state senate seat in a special election shortly after being elected
	save ../data_output/legislators_CA_house_1993_2016.dta, replace
	
	gen e_won = elections_won
	qui forval i= 1960(2)1988{ //years enacted
		replace e_won = subinstr(e_won, "`i';", "",.)
	}
	tostring year, gen(regexp)
	replace regexp = "^([^!]+"+regexp+";)"
	gen previous_wins = regexs(1) if regexm(e_won, regexp)
	egen prev_terms = noccur(previous_wins) , string(";")
	gen termlimited=prev_terms==3
	save ../data_output/legislators_CA_house_1993_2016.dta, replace
project , creates(../data_output/legislators_CA_house_1993_2016.dta)
	

	
	** California merge with bill data
project, original(../data_input/CA/bill_sponsors_california.xlsx)	
	import excel using ../data_input/CA/bill_sponsors_california.xlsx, first clear
	rename Author sponsor
	rename Measure bill
	rename Subject subject
	rename Status status
	keep sponsor bill term subject status
	tempfile tmp
	save `tmp'
	
project, original(../data_input/CA/ca_bills.csv)		
	**1993-1998 bills
	import delimited using ../data_input/CA/ca_bills.csv,  clear    encoding("utf-8")  bindquotes(strict) delimiters(";")  varnames(1)
	forval i = 1 (1) 10 {
		replace subject = trim(usubinstr(subject, "  "," ",.))
	}
	gen term = subinstr(session, "_","-",.)
	append using `tmp'
	
	replace sponsor = usubinstr(sponsor, "é","e",.) 
	replace sponsor = usubinstr(sponsor, "á","a",.)
	replace sponsor = usubinstr(sponsor, "-","",.)
	replace sponsor = proper(sponsor)
	drop if regexm(lower(sponsor), "committee") | sponsor==""
	gen mergename = lower(sponsor)
	drop session
	replace mergename = "millendermcdonald" if mergename=="mcdonald" & regexm(term, "1993|1995")
	replace mergename = "oconnell" if mergename=="o'connell" & regexm(term, "1993")
	replace mergename = "bustamente" if mergename=="bustamante" & regexm(term, "1995|1997")
	
	merge m:1 mergename term using ../data_output/legislators_CA_house_1993_2016.dta
	//4 obs are not merging (see description above for Jan Leja, Richard Alacorn, Don Perata and bruce bronzan)
	
	drop if _merge==1
	drop sponsor	
	gen sponsor = 1 if bill!=""
	gen law = regexm(lower(status),"chapter")
	save ../data_output/bills_by_sponsor_CA_1993_2016.dta, replace
project , creates(../data_output/bills_by_sponsor_CA_1993_2016.dta)
	
	use ../data_output/bills_by_sponsor_CA_1993_2016.dta, clear
	collapse (sum) sponsor law (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	replace law = . if regexm(term , "1993|1995|1997")
	save ../data_output/total_bills_by_sponsor_CA_1993_2016.dta, replace
project , creates(../data_output/total_bills_by_sponsor_CA_1993_2016.dta)

	//960 observations = 80*12 terms


	**VOTING
	
		
	**voting in CA
	
	use ../data_output/legislators_CA_house_1993_2016.dta, clear
	drop if chamber=="senate"
	keep if regexm(term, "1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen os_mergename = regexs(1) if regexm(lower(CandName),"(^[^,]+),")
	bys os_mergename term: gen n = _n
	by os_mergename term: egen mean_n=mean(n)
	replace os_mergename = regexs(2) + " " + regexs(1) if mean_n>1	& regexm(lower(CandName),"(^[^,]+), ([^ ]+)")
	drop n mean_n
	tempfile tmp
	save `tmp'
	
	
project , original(../data_input/CA/ca_votes.csv)	
	**data on voting
	import delimited using ../data_input/CA/ca_votes.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	gen floor= location=="Assembly Floor"
	rename session term
	keep if regexm(term, "1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen os_mergename = name //regexs(1) if regexm(name, " ([^ ]+)$")
	replace os_mergename = lower(os_mergename)
	replace os_mergename = subinstr(os_mergename,"’","",.)
	replace os_mergename = subinstr(os_mergename,"'","",.)
	replace os_mergename = subinstr(os_mergename,"-","",.)
	replace os_mergename = "lamalfa" if os_mergename=="la malfa" & regexm(term, "2003|2005|2007")
	replace os_mergename = "lasuer" if os_mergename=="la suer" & regexm(term, "2001|2003|2005")
	replace os_mergename = "negretemcleod" if os_mergename=="negrete mcleod" & regexm(term, "2001|2003|2005")
	replace os_mergename = "gaines" if os_mergename=="beth gaines" & regexm(term, "2013|2015")
	replace os_mergename = "lowenthal" if os_mergename=="bonnie lowenthal" & regexm(term, "2009|2011")
	replace os_mergename = "talamanteseggman" if os_mergename=="eggman" & regexm(term, "2013|2015")
	replace os_mergename = "runner" if os_mergename=="sharon runner" & regexm(term, "2005|2007")
	replace os_mergename = "strickland" if os_mergename=="audra strickland" & regexm(term, "2009")
	replace os_mergename = "berryhill" if os_mergename=="bill berryhill" & regexm(term, "2011")
	replace os_mergename = "thomas berryhill" if os_mergename=="tom berryhill" & regexm(term, "2009")
	replace os_mergename = "allen" if os_mergename=="travis allen" & regexm(term, "2015")
	replace os_mergename = "delatorre" if os_mergename=="de la torre" & regexm(term, "2005|2007|2009")
	replace os_mergename = "deleon" if os_mergename=="de leon" & regexm(term, "2007|2009")
	replace os_mergename =  "calderon" if os_mergename=="charles calderon" & regexm(term, "2007|2009|2011")
	replace os_mergename =  "stone" if os_mergename=="mark stone" & regexm(term, "2015")
	replace os_mergename =  "john perez" if os_mergename=="john a. perez" & regexm(term, "2009")
	replace os_mergename =  "manuel perez" if os_mergename=="v. manuel perez" & regexm(term, "2009")	
	merge m:1 os_mergename term using `tmp'
	
	// obs not merging
	drop if _merge==1
	drop _merge	
	egen vote_id = group(bill location date motion)
	
	gen floorvotes = 0 if floor==1
	replace floorvotes= 1 if regexm(lower(vote), "yes|no")  & floor==1
	gen cmtvotes = 0 if floor==0
	replace cmtvotes= 1 if regexm(lower(vote), "yes|no")  & floor==0
	replace vote = "1" if lower(vote)=="yes"
	replace vote = "-1" if lower(vote)=="no"
	replace vote = "0" if lower(vote)=="other"
	destring vote, replace
	bys party vote_id : egen party_mean=mean(vote)
	gen party_vote = 0
	replace party_vote = 1 if party_mean>0
	replace party_vote = -1 if party_mean<0
	gen vote_party_line = vote==party_vote
	gen vote_against_party = vote==-party_vote	
	drop if regexm(location, "^Sen")
	
	*floor votes
	preserve
	keep if floor==1
	bys vote_id: egen votemargin = mean(vote)
	gen close_floorvotes = floorvotes if abs(votemargin)<.1		
	save ../data_output/floorvotes_CA_1999_2016.dta, replace
project , creates(../data_output/floorvotes_CA_1999_2016.dta)	

	use ../data_output/floorvotes_CA_1999_2016.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum) *floorvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	save ../data_output/total_floorvotes_CA_1999_2016.dta, replace
project , creates(../data_output/total_floorvotes_CA_1999_2016.dta)	
	
	restore

	*cmt votes
	drop if floor==1
	save ../data_output/cmtvotes_CA_1999_2016.dta, replace
project , creates(../data_output/cmtvotes_CA_1999_2016.dta)	

	use ../data_output/cmtvotes_CA_1999_2016.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum) cmtvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	save ../data_output/total_cmtvotes_CA_1999_2016.dta, replace	
	
project , creates(../data_output/total_cmtvotes_CA_1999_2016.dta)
